#!/usr/bin/python3


import log
import logging

import pymysql

info = logging.INFO
error = logging.ERROR
debug = logging.DEBUG
warn = logging.WARN

class CMySqlDB:
    def __init__(self, log, dbHost="10.1.54.50", dbPort=3306,
                 dbUser="shengli", dbPwd="shengli0",
                 dbName="system_information_management"):
        self.log = log
        self.dbHost = dbHost
        self.dbPort = dbPort
        self.dbUser = dbUser
        self.dbUserPwd = dbPwd
        self.dbName = dbName

    def init(self):
        try:
            self.mysqlDB = pymysql.connect(host=self.dbHost, port=self.dbPort, user=self.dbUser, passwd=self.dbUserPwd, database=self.dbName)
        #time.sleep(1)
            self.cursor = self.mysqlDB.cursor()
        except:
            self.log.log("connect to mysql database fialed:" + self.dbHost + ":" + str(self.dbPort), error)

    def close(self):
        try:
            self.cursor.close()
            self.mysqlDB.close()
        except:
            return

    def queryTableColum(self, schema, table):
        try:
            self.init()
            self.cursor.execute("select concat(column_name,'') from information_schema.columns where table_schema = '"\
                                + schema + "' and table_name ='" +table + "'")
        except pymysql.Error as e:
            self.mysqlDB.rollback()
            self.log.log("mysql query table colum failed，schema：" + schema + " table:" + table + " error:" + str(e), error)

    def query(self, sqlStr):
        try:
            self.init()
            self.cursor.execute(sqlStr)
            #self.log.log(sqlStr)
        except pymysql.Error as e:
            self.mysqlDB.rollback()
            self.log.log("mysql query failed，sql：" + sqlStr + " error:" + str(e), error)

    def exec(self, execStr):
        try:
            self.init()
            self.cursor.execute(execStr)
            #self.log.log(execStr)
            self.mysqlDB.commit()
            self.cursor.close()
            self.mysqlDB.close()
            self.log.log("mysql exec: " + execStr)
            return True
        except pymysql.Error as e:
            self.mysqlDB.rollback()
            self.log.log("mysql exec failed，sql：" + execStr + " error:" + str(e), error)
            return False

    def getData(self):
        try:
            rows = self.cursor.fetchall()
            return rows
        except:
            self.log.log("mysql get data failed", error)
            return None






#已测试
# testLog = CLog(logName="test", isPrint=True)
# print("test")
# testDB = CMySqlDB(log=testLog, dbHost="10.1.54.50", dbUser="shengli", dbPwd="shengli0", dbName="system_information_management")
# time.sleep(1)
# testDB.query(sqlStr="select * from t_user")
# data = testDB.getData()
# print(data)
#
# #testDB.exec(execStr="update t_user set pwd = 2 where user = 'jiang.yushu'")
# testDB.query(sqlStr="select * from t_user")
# datas = testDB.getData()
# print(data)
#
# userInfo = { "userName" : ["pwd", "", "", "", "", ""]}
# for row in datas:
#     userInfo[row[1] ] = [row[2], row[3], row[4], row[5], row[6], row[7] ]
#
# print (userInfo)



